package cn.gson.crm.model.dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import cn.gson.crm.model.Jdbc;
import cn.gson.crm.model.entity.Graduate;
import cn.gson.crm.model.entity.Student;
import cn.gson.crm.vo.GraduateVo;

public class GraduateDao {
	/**
	 * 从学生表获取数据，新建一个毕业生
	 */
	public boolean insertGraduate(Student s,String password) throws SQLException {
		String sql = "INSERT INTO `graduate` " + "(`grad_id`, `grad_name`, `grad_gender`,  "
				+ "`grad_telphone`, `grad_qq`,  `grad_password`,  `grad_status_id`) " + "VALUES (?,?,?,?,?,?,7);";
		boolean result = Jdbc.getInstace().executeUpdate(sql, s.getStuId(), s.getStuName(), s.getStuGender(),
				s.getStuTel(), s.getStuQQ(),password);
		return result;
	}

	/**
	 * 获得所有的毕业生列表
	 * 
	 * @return
	 * @throws SQLException
	 */
	public List<GraduateVo> getAllGraduate() throws SQLException {
		String sql = "SELECT graduate.*, `status`.status_name," + "(SELECT count(*) FROM graduaterecord WHERE "
				+ "graduaterecord.grad_id = graduate.grad_id ) " + "num FROM graduate INNER JOIN `status` ON "
				+ "graduate.grad_status_id = `status`.status_id";
		List<Map<String, Object>> data = Jdbc.getInstace().executeQueryForList(sql);
		List<GraduateVo> graduateVos = new ArrayList<>();
		GraduateVo graduate = null;
		if (data != null && !data.isEmpty()) {
			for (Map<String, Object> map : data) {
				graduate = builderGraduateVo(map);
				graduateVos.add(graduate);
			}
		}
		return graduateVos;

	}

	/**
	 * 查询一个毕业生根据id
	 * 
	 * @param id
	 * @return
	 * @throws SQLException
	 */
	public Graduate getOneGraduate(String id) throws SQLException {
		String sql = "select * from graduate where grad_id=?";
		Map<String, Object> map = Jdbc.getInstace().executeQuery(sql, id);
		Graduate graduate = builderGraduate(map);
		return graduate;
	}

	/**
	 * 更新一个毕业生的就业信息
	 */
	public boolean updateGraduateById(String id,Date date, String address, String company, String salary) throws SQLException {
		String sql = "UPDATE `crm`.`graduate` SET `grad_workplace`=?, `grad_salary`=?,`grad_company`=?,`grad_job_time`=?,  `grad_status_id`='9' WHERE (`grad_id`=?);";
		boolean result = Jdbc.getInstace().executeUpdate(sql, address, salary, company,date, id);
		return result;
	}

	/**
	 * 根据毕业生id插入一条记录，有方式，有内容
	 * 
	 * @return
	 * @throws SQLException
	 */
	public boolean insertRecord(String id, String way, String content) throws SQLException {
		String sql = "INSERT INTO `crm`.`graduaterecord` ( `grad_way`, `grad_record`, `grad_id`) VALUES ( ?,?, ?);";
		boolean result = Jdbc.getInstace().executeUpdate(sql, way, content, id);
		return result;
	}

	/**
	 * 根据毕业生的id获得所有的记录
	 * @return
	 * @throws SQLException 
	 */
	public List<Map<String, Object>> getAllGraduateRecordById(String id) throws SQLException {
		String sql="select * from graduaterecord where grad_id=?";
		List<Map<String, Object>> data=Jdbc.getInstace().executeQueryForList(sql, id);
		return data;
	}

	// 数据库字段名
	// `grad_name`
	// `grad_gender`
	// `grad_workplace`
	// `grad_salary`
	// `grad_telphone`
	// `grad_qq`
	// `grad_way`
	// `grad_company`
	// `grad_message`
	// `grad_status_id`

	private Graduate builderGraduate(Map<String, Object> map) {
		Graduate graduate = new Graduate();
		graduate.setGradId((Integer) map.get("grad_id"));
		graduate.setGradName((String) map.get("grad_name"));
		graduate.setGradGender((String) map.get("grad_gender"));
		graduate.setGradWorkplace((String) map.get("grad_workplace"));
		graduate.setGradSalary((String) map.get("grad_salary"));
		graduate.setGradTelphone((String) map.get("grad_telphone"));
		graduate.setGradQQ((String) map.get("grad_qq"));
		graduate.setGradWay((Integer) map.get("grad_way"));
		graduate.setGradCompany((String) map.get("grad_company"));
		graduate.setGradStatusId((Integer) map.get("grad_status_id"));
		graduate.setGradJobTime((Date) map.get("grad_job_time"));
		return graduate;

	}

	private GraduateVo builderGraduateVo(Map<String, Object> map) {
		GraduateVo graduateVo = new GraduateVo();
		graduateVo.setGradId((Integer) map.get("grad_id"));
		graduateVo.setGradName((String) map.get("grad_name"));
		graduateVo.setGradGender((String) map.get("grad_gender"));
		graduateVo.setGradWorkplace((String) map.get("grad_workplace"));
		graduateVo.setGradSalary((String) map.get("grad_salary"));
		graduateVo.setGradTelphone((String) map.get("grad_telphone"));
		graduateVo.setGradQQ((String) map.get("grad_qq"));
		graduateVo.setGradWay((Integer) map.get("grad_way"));
		graduateVo.setGradCompany((String) map.get("grad_company"));
		graduateVo.setGradStatusId((Integer) map.get("grad_status_id"));
		graduateVo.setStatueName((String) map.get("status_name"));
		graduateVo.setRecordCount((long) map.get("num"));
		graduateVo.setGradJobTime((Date) map.get("grad_job_time"));
		return graduateVo;

	}
}
